# -*- coding: utf-8 -*-
'''
@author: olivia.dou
Created on: 2018/11/29 13:51
desc:
'''

import unittest,logging,re,json
#import xlrd
import pandas as pd
from base.get_config import get_testcase_file
from base.expression_evaluation import eval_from_string
import base.globalvars as glo
from base import ddt
from util.db_util import get_query_string,execute_query

#从Excel test case文件中读取数据，用以驱动测试
tc_file_path = get_testcase_file()
xl = pd.ExcelFile(tc_file_path)
sheetnames = xl.sheet_names
#book = xlrd.open_workbook(tc_file_path, encoding_override='gb18030')
datafrm = pd.DataFrame()
for sheetname in sheetnames:
    #data = xl.parse(sheetname)
    data = pd.read_excel(tc_file_path,sheetname)
    #data = pd.read_excel(book, sheetname,engine='xlrd')
    datafrm = datafrm.append(data)

#datafrm = pd.read_excel(get_testcase_file())
datafrm = datafrm.fillna(method = 'ffill') # 合并单元格的部分填充
datafrm = datafrm.fillna('') #将空值统一替换为空字符串
testdata = []
datafrm.apply(lambda x: testdata.append(x.to_dict()), axis=1) #将每一行转换为字典，所有行组成一个字典的列表

@ddt.ddt
class DBTest(unittest.TestCase):

    @ddt.data(*testdata)
    def test_db(self, casedata):
        if (casedata['适用环境']=='All' or glo.get_value("environment") in casedata['适用环境'].split(',')) and casedata['是否运行'].upper() == 'Y':  # 如果未标注运行，则该testcase跳过
            step = casedata['验证步骤']
            sql = get_query_string(step).rstrip(';')  #需要把末尾的分号，去除，否则报unable to execute query, ORA-00911: 无效字符
            sql = eval_from_string(sql)
            logging.debug(sql)
            #print(sql)
            if sql !="":
                resultset = execute_query(sql)

                if resultset is None: # sql执行出错
                    self.assertTrue(1==0,"SQL 执行出错")
                if isinstance((casedata['预期结果']),int):
                    self.assertEqual(resultset, [(casedata['预期结果'],)], "预期结果为%s，实际为%s"%(casedata['预期结果'],resultset[0][0]))
                elif re.match('\[.*\]',casedata['预期结果'].strip()):
                    results = [result[0] for result in resultset]
                    expected = json.loads(casedata['预期结果'].strip())
                    self.assertEqual(results,expected,"预期结果为%s,实际结果为%s"%(repr(expected),repr(results)))
                elif casedata['预期结果'].strip().lower()=='not null':
                    self.assertNotEqual(resultset,[],"预期结果非空，实际为空")
                elif casedata['预期结果'].strip().lower()=='is null':  # 'null'会被pandas作为空值读入
                    self.assertEqual(resultset,[],"预期结果为空，实际不为空")
                elif casedata['预期结果'].strip().startswith('>='):
                    self.assertGreaterEqual(resultset[0][0],int(casedata['预期结果'].replace('>=','').strip()))
                elif casedata['预期结果'].strip().startswith('<='):
                    self.assertLessEqual(resultset[0][0],int(casedata['预期结果'].replace('<=','').strip()))
                elif casedata['预期结果'].strip().startswith('>'):
                    self.assertGreater(resultset[0][0],int(casedata['预期结果'].replace('>','').strip()))
                elif casedata['预期结果'].strip().startswith('<>'):
                    self.assertNotEqual(resultset[0][0],int(casedata['预期结果'].replace('<>','').strip()))
                elif casedata['预期结果'].strip().startswith('<'):
                    self.assertGreaterEqual(resultset[0][0],int(casedata['预期结果'].replace('<','').strip()))

        else:
            raise unittest.SkipTest("case set not to run")  # case真正跳过，不会被统计为pass
